[#ftl]
[#assign tab = tab /]
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
[#assign ns = tab.fullTypeName?substring(0 , tab.fullTypeName?last_index_of(".")) /]
[#assign ns = ns?substring(0 , ns?last_index_of(".")) /]
[#assign ns = ns + ".dao." + tab.typeName + "Dao" /]
<mapper namespace="${ns}">

<!-- =========================== RETRIEVE ========================== -->
<select id="find" resultMap="baseRM">
    <bind name="_parameter" value="${'#'}{'id':_parameter}" />
    <include refid="queryBase2" />
</select>

<select id="findOne" parameterType="map" resultMap="baseRM">
    <include refid="queryBase2"/>
</select>

[#macro cols prop tab]
    [#list tab.ids + tab.columns as col]
        [#if x!false],[#else][#assign x = true /] [/#if]${prop}.${col.column} AS [#if prop != 'ROOT']${prop}_[/#if]${col.column}
    [/#list]
[/#macro]

<!-- -->
<select id="findMany" parameterType="map" resultMap="baseRM">
[#-- 是否存在 M:N 关系 --]
[#assign fetchMany = false/]
[#list tab.oneToMany + tab.manyToMany as many]
    [#if many.fetch && many.rel.columns?size > 0]
        [#assign fetchMany = true /][#break]
    [/#if]
[/#list]
[#-- 如果存在 M:N 关系, 加入级联头 --]
[#if fetchMany!false]
    SELECT
    FD.*[#if fetchMany!false],[/#if]
    [#assign x = false /]
    [#list tab.oneToMany + tab.manyToMany as many][#if many.fetch][@cols many.property many.rel /][/#if][/#list]
    FROM (
[/#if]
    <include refid="support.ph"/>
    <include refid="queryBase"/>
    <include refid="support.pf"/>
[#-- 如果存在 M:N 关系, 加入级联尾 --]
[#if fetchMany!false]
    ) FD
    [#-- 级联查询的一对多 --]
    [#list tab.oneToMany as many]
        [#if many.fetch]
            [#if many.optional]LEFT[#else]INNER[/#if] JOIN ${many.rel.table} ${many.property} ON ${many.property}.${many.computedJoinColumn} = FD.ID AND ${many.property}.DELETED = 0[#-- TODO for glanway --]
        [/#if]
    [/#list]
    [#-- 级联查询所有的多对多 --]
    [#list tab.manyToMany as many]
        [#if many.fetch]
            [#assign jt = many.computedJoinTable /]
            [#assign rel = many.rel /]
            [#assign prop = many.property /]
            [#if many.optional]LEFT[#else]INNER[/#if] JOIN ${jt.name} ON ${jt.name}.${jt.joinColumn} = FD.ID AND ${jt.name}.DELETED = 0 [#-- TODO for glanway --]
            [#if many.optional]LEFT[#else]INNER[/#if] JOIN ${many.rel.table} ${prop} ON ${jt.name}.${jt.inverseJoinColumn} = ${prop}.ID AND ${jt.name}.DELETED = 0 AND ${prop}.DELETED = 0 [#-- TODO for glanway --]
        [/#if]
    [/#list]
    ORDER BY
    <!-- TODO 这里有bug, 只能先按照ROOT排序,然后是 one to one 然后 many to one ... 求助... -->
    <bind name="_sort" value="_oldSort"/>
    <bind name="_sortPrefix" value="'FD.'"/>
    <trim prefixOverrides=","><include refid="support.dynamicSort"/>, </trim>
    <!-- to one -->
    [#list tab.oneToOne + tab.manyToOne as one]
        <bind name="_sort" value="_parameter.${one.property} != null ? ${one.property}._sort : null" />
        <bind name="_sortPrefix" value="'FD.${one.property}_'"/>
        <trim prefixOverrides=","><include refid="support.dynamicSort"/>, </trim>
    [/#list]
    <!-- to many -->
    [#list tab.oneToMany + tab.manyToMany as many]
        <bind name="_sort" value="_parameter.${many.property} != null ? ${many.property}._sort : null" />
        <bind name="_sortPrefix" value="'${many.property}.'"/>
        <trim prefixOverrides=","><include refid="support.dynamicSort"/>, </trim>
    [/#list]
    FD.ID
[/#if]
</select>

<select id="count" parameterType="map" resultType="int">
    SELECT COUNT(1) FROM (
    <include refid="queryBase"/>
    ) C [#-- mysql 必须有别名 --]
</select>

<!-- =========================== CREATE ========================== -->

<insert id="save" parameterType="${tab.typeName}">
    <selectKey keyProperty="id" resultType="string" order="BEFORE">
        SELECT UUID()
    </selectKey>
    INSERT INTO ${tab.table} (
    <!-- self columns -->
[#list tab.ids + tab.columns as col]
    [#if col_index > 0],[/#if]${col.column}
[/#list]
    <!-- one to one fk -->
[#list tab.oneToOne as one]
    [#if one.mappedBy?has_content][#else]
        , ${one.computedJoinColumn}
    [/#if]
[/#list]
    <!-- many to one fk -->
[#list tab.manyToOne as one]
    , ${one.joinColumn}
[/#list]
    )
    VALUES (
    <!-- self columns -->
[#list tab.ids + tab.columns as col]
${'#'}{${col.property}}[#if col_has_next],[/#if]
[/#list]
    <!-- one to one fk -->
[#list tab.oneToOne as one]
    [#if one.mappedBy?has_content][#else]
        , ${'#'}{${one.property}.id}
    [/#if]
[/#list]
    <!-- may to one fk -->
[#list tab.manyToOne as one], ${'#'}{${one.property}.id}[/#list]
    )
</insert>

<!-- =========================== UPDATE ========================== -->

<update id="update" parameterType="${tab.typeName}">
    UPDATE ${tab.table}
    <set>
    [#list tab.columns as col]
        <if test="${col.property} != null">
        ${col.column} = ${'#'}{${col.property}},
        </if>
    [/#list]
    [#-- one to one --]
    [#list tab.oneToOne as one]
        [#if one.mappedBy?has_content][#else]
            <if test="${one.property} != null and ${one.property}.id != null">
            ${one.computedJoinColumn} = ${'#'}{${one.property}.id},
            </if>
        [/#if]
    [/#list]
    [#-- many to one --]
    [#list tab.manyToOne as one]
        <if test="${one.property} != null and ${one.property}.id != null">
        ${one.computedJoinColumn} = ${'#'}{_parameter.${one.property}.id},
        </if>
    [/#list]
    </set>
    WHERE ID = ${'#'}{id}
</update>

[#--
<delete id="delete">
    DELETE FROM ${tab.table} WHERE ID = ${'#'}{id}
</delete>
--]
[#-- TODO for glanway --]
<update id="delete">
    UPDATE ${tab.table} SET DELETED = 1 WHERE ID = ${'#'}{id}
</update>

<!-- =========================== SQL FRAGMENT ========================== -->
[#-- 过滤条件 --]
[#macro filter prop]
<!-- ${prop} filter -->
<include refid="${prop}Filter"/>
<!-- ${prop} dynamic filter -->
<bind name="_filters" value="_parameter.${prop} != null ? ${prop}._filters : null "/>
<bind name="_filterPrefix" value="'${prop}.'"/>
<trim suffixOverrides="AND">AND
    <include refid="support.dynamicFilters"/>
</trim>
[/#macro]

<sql id="queryBase">
    <bind name="_oldFilters" value="_parameter._filters"/>
    <bind name="_oldSort" value="_parameter._sort"/>
    <!-- 因为 mybatis 在当前上下文找不到变量时候会到 _parameter 中是否存在, 因此这里需要清空(name不会作为ognl解析) -->
    <bind name="" value="_parameter._filters = null"/>
    <bind name="" value="_parameter._sort = null"/>
    SELECT
[#assign x = false /]
[@cols 'ROOT' tab /]
[#list tab.oneToOne as one][#if one.fetch][@cols one.property one.rel /][/#if][/#list]
[#list tab.manyToOne as one][#if one.fetch][@cols one.property one.rel /][/#if][/#list]
    FROM ${tab.table} ROOT
[#-- one to one --]
[#list tab.oneToOne as one]
    [#if one.fetch]
        [#--[#if one.optional]LEFT[#else]INNER[/#if] JOIN--]
        [#if one.optional]
            <!-- 当M:N, N可选 (LEFT JOIN), 当通过N来筛选M, 此时 N 变为必选(INNER JOIN或exists后LEFT JOIN) -->
            <choose>
                <when test="_parameter.${one.property} != null and (${one.property}._filters == null ? ${one.property}.size > 0 : (${one.property}.size > 1 or ${one.property}._filters.size() > 0))">INNER JOIN</when>
                <otherwise>LEFT JOIN</otherwise>
            </choose>
        [#else]
            INNER JOIN
        [/#if]
        ${one.rel.table} ${one.property} ON
        [#-- 如果root是不关系维护方 --]
        [#if one.mappedBy?has_content]
        ${one.property}.${one.computedJoinColumn} = ROOT.ID
        [#else]
        ${one.property}.ID = ROOT.${one.computedJoinColumn}
        [/#if]
        [@filter one.property /]
    [/#if]
[/#list]
[#-- many to one --]
[#list tab.manyToOne as one]
    [#if one.fetch]
        [#--[#if one.optional]LEFT[#else]INNER[/#if] JOIN--]
        [#if one.optional]
            <!-- 当M:N, N可选 (LEFT JOIN), 当通过N来筛选M, 此时 N 变为必选(INNER JOIN或exists后LEFT JOIN) -->
            <choose>
                <when test="_parameter.${one.property} != null and (${one.property}._filters == null ? ${one.property}.size > 0 : (${one.property}.size > 1 or ${one.property}._filters.size() > 0))">INNER JOIN</when>
                <otherwise>LEFT JOIN</otherwise>
            </choose>
        [#else]
            INNER JOIN
        [/#if]
        ${one.rel.table} ${one.property} ON
        ROOT.${one.computedJoinColumn} = ${one.property}.ID
        [@filter one.property /]
    [/#if]
[/#list]
    WHERE 1 = 1
    <!-- self filter -->
    <include refid="rootFilter"/>
    <!-- self dynamic filter -->
    <bind name="_filters" value="_oldFilters"/>
    <bind name="_filterPrefix" value="'ROOT.'"/>
    <trim suffixOverrides="AND">AND
        <include refid="support.dynamicFilters"/>
    </trim>

[#-- one to many --]
[#list tab.oneToMany as many]
[#--[#if many.fetch]--]
    <if test="_parameter.${many.property} != null">
        AND EXISTS (
        SELECT 1
        FROM ${many.rel.table} ${many.property}
        WHERE ${many.property}.${many.computedJoinColumn} = ROOT.ID
        [@filter many.property /]
        )
    </if>
[#--[/#if]--]
[/#list]
[#list tab.manyToMany as many]
[#--[#if many.fetch]--]
    [#assign jt = many.computedJoinTable /]
    [#assign rel = many.rel /]
    [#assign prop = many.property /]
    <if test="_parameter.${many.property} != null">
        AND EXISTS (
        SELECT 1
        FROM
    ${rel.table} ${prop}, ${jt.name}
        WHERE
    ${prop}.ID = ${jt.name}.${jt.inverseJoinColumn}
        AND
    ${jt.name}.${jt.joinColumn} = ROOT.ID
        [@filter prop /]
        )
    </if>
[/#list]
ORDER BY
    <!-- TODO 这里有bug, 只能先按照ROOT排序,然后是 one to one 然后 many to one ... 求助... -->
    <bind name="_sort" value="_oldSort"/>
    <bind name="_sortPrefix" value="'ROOT.'"/>
    <trim prefixOverrides=","><include refid="support.dynamicSort"/>, </trim>
    <!-- to one -->
[#list tab.oneToOne + tab.manyToOne as one]
    <bind name="_sort" value="_parameter.${one.property} != null ? ${one.property}._sort : null" />
    <bind name="_sortPrefix" value="'${one.property}.'"/>
    <trim prefixOverrides=","><include refid="support.dynamicSort"/>, </trim>
[/#list]
    ROOT.ID
</sql>

<sql id="queryBase2">
[#assign fetchMany = false/]
[#list tab.oneToMany + tab.manyToMany as many]
    [#if many.fetch && many.rel.columns?size > 0]
        [#assign fetchMany = true /][#break]
    [/#if]
[/#list]
[#if fetchMany!false]
    SELECT
    FD.*[#if fetchMany!false],[/#if]
    [#assign x = false /]
    [#list tab.oneToMany + tab.manyToMany as many][#if many.fetch][@cols many.property many.rel /][/#if][/#list]
    FROM (
[/#if]
    <include refid="queryBase"/>
[#if fetchMany!false]
    ) FD
    [#list tab.oneToMany as many]
        [#if many.fetch]
            [#if many.optional]LEFT[#else]INNER[/#if] JOIN ${many.rel.table} ${many.property} ON ${many.property}.${many.computedJoinColumn} = FD.ID AND ${many.property}.DELETED = 0[#-- TODO for glanway --]
        [/#if]
    [/#list]
    [#list tab.manyToMany as many]
        [#if many.fetch]
            [#assign jt = many.computedJoinTable /]
            [#assign rel = many.rel /]
            [#assign prop = many.property /]
            [#if many.optional]LEFT[#else]INNER[/#if] JOIN ${jt.name} ON ${jt.name}.${jt.joinColumn} = FD.ID AND ${jt.name}.DELETED = 0 [#-- TODO for glanway --]
            [#if many.optional]LEFT[#else]INNER[/#if] JOIN ${many.rel.table} ${prop} ON ${jt.name}.${jt.inverseJoinColumn} = ${prop}.ID AND ${jt.name}.DELETED = 0 AND ${prop}.DELETED = 0 [#-- TODO for glanway --]
        [/#if]
    [/#list]
[/#if]
</sql>

<sql id="rootFilter">
[#list tab.ids + tab.columns as col]
    <if test="_parameter.${col.property} != null">
        AND ROOT.${col.column} = ${'#'}{_parameter.${col.property}}
    </if>
[/#list]
    AND ROOT.DELETED = 0 [#-- TODO for glanway --]
</sql>

[#macro filterSql prop tab]
<!-- ${prop} base filter -->
<sql id="${prop}Filter">
    <if test="_parameter.${prop} != null">
        [#list tab.ids + tab.columns as col]
            <if test="${prop}.${col.property} != null">
                AND ${prop}.${col.column} = ${'#'}{${prop}.${col.property}}
            </if>
        [/#list]
    </if>
    AND ${prop}.DELETED = 0 [#-- TODO for glanway --]
</sql>
[/#macro]
[#list tab.oneToOne + tab.manyToOne + tab.oneToMany + tab.manyToMany as rel]
    [@filterSql rel.property rel.rel /]
[/#list]

[#macro colMapping prefix tab]
    [#list tab.ids as col]
    <id property="${col.property}" column="${prefix}_${col.column}"/>
    [/#list]
    [#list tab.columns as col]
    <result property="${col.property}" column="${prefix}_${col.column}"/>
    [/#list]
[/#macro]

<!-- ======================== RESULT MAP ========================= -->
<resultMap id="baseRM" type="${tab.typeName}">
[#list tab.ids as col]
    <id property="${col.property}" column="${col.column}"/>
[/#list]
[#list tab.ids + tab.columns as col]
    <result property="${col.property}" column="${col.column}"/>
[/#list]
    <!-- to one -->
[#list tab.oneToOne + tab.manyToOne as one]
    [#--
    <association property="${one.property}" javaType="${one.rel.fullTypeName}">
    --]
    <association property="${one.property}" javaType="${one.rel.typeName}">
        [@colMapping one.property one.rel /]
    </association>
[/#list]
    <!-- to many -->
[#list tab.oneToMany + tab.manyToMany as many]
    [#--
    <collection property="${many.property}" ofType="${many.rel.fullTypeName}">
    --]
    <collection property="${many.property}" ofType="${many.rel.typeName}">
        [@colMapping many.property many.rel /]
    </collection>
[/#list]
</resultMap>
</mapper>